-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO LOAD FROM temp_batch_trans_t TO batch_trans_t  


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION batch_trans_upload_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_trans_received integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN


SELECT INTO v_batch_run_date our_run_date from temp_batch_trans_t limit 1;
SELECT INTO v_cust_id cust_id from temp_batch_trans_t limit 1;

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,2,'batch_trans_t LOADING');



-- STEP 1 - SCRATCH batch_trans_t 
-- 

TRUNCATE batch_trans_t;




-- STEP 2 - INSERTING INTO batch_trans_t ONLY IF:
--   a) THIS IS A NEW TRANSACTIONS (NEW cust_id,hid,acctnum,trans_id COMBINATION)
--   b) WE HAD FOUND ACTIVE INSURANCE ON THAT ACCOUNT (was in insurance2_results_t with active_flag = 1 and suppress_flag = 0)
--   c) THE TRANSACTION AMOUNT IS NOT ZERO 


RAISE NOTICE 'START UPLOAD OF ACCOUNTS IN batch_trans_t';

INSERT INTO batch_trans_t
SELECT
  batch_trans.cust_id               ,
  batch_trans.our_run_date          ,
  batch_trans.seqnum                ,
  batch_trans.hid                   ,
  batch_trans.acctnum               ,
  batch_trans.trans_id              ,
  batch_trans.trans_date            ,
  batch_trans.trans_amt             ,
  batch_trans.trans_type            ,
  batch_trans.cdm                   ,
  batch_trans.payor_plan            ,
  batch_trans.trans_type_detail     ,
  batch_trans.bill_date             ,
  batch_trans.denial_code           ,
  batch_trans.denial_date           ,
  batch_trans.medlytix_denial_code  ,
  results.file_date                 ,
  results.medlytix_ins_type         ,
  batch_trans.medlytix_recon_flag   ,
  batch_trans.medlytix_recon_date 

FROM

  (SELECT
    c.*
  FROM

     (SELECT
        a.*,
        b.cust_id as flag 
      FROM

    	(SELECT
         	*
     	FROM 
         	temp_batch_trans_t
    	) as a

    	LEFT JOIN

    	(SELECT
        	cust_id,hid,acctnum,trans_id
     	FROM
        	trans_t
    	WHERE
        	cust_id = v_cust_id
    	) as b

    	ON (a.cust_id = b.cust_id AND a.hid = b.hid AND a.acctnum = b.acctnum AND a.trans_id = b.trans_id)

     ) as c 

  WHERE c.flag IS NULL

  ) as batch_trans

  JOIN

  (SELECT
      distinct on (cust_id,hid,acctnum) cust_id,hid,acctnum,file_date,medlytix_ins_type
   FROM
      insurance2_results_t
   WHERE
      cust_id = v_cust_id AND active_flag = 1 AND suppress_flag = 0
   ORDER BY
      cust_id,hid,acctnum,file_date

   ) as results 

  ON (batch_trans.cust_id = results.cust_id AND batch_trans.hid = results.hid AND batch_trans.acctnum = results.acctnum)
  
;



GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH LOADING batch_trans_t TABLE : %', v_status;



SELECT INTO v_batch_trans_received count(*) from temp_batch_trans_t ;

UPDATE batch_status_trans_t
SET
   temp_batch_trans_received  = v_batch_trans_received,
   batch_trans_inserted       = v_status
 WHERE
  (batch_status_trans_t.last_run_date = v_batch_run_date and batch_status_trans_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,2,'FINISH LOADING OF batch_trans_t');


  return v_status;
END;
$$ LANGUAGE plpgsql;
